using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class InstrumentRecordDataAccess {
             
            public static InstrumentRecord GetById(int id){
                var sql = @"select * from dbo.InstrumentRecord where InstrumentRecordID = @id ";
                var paramList = new[] {
                    new SqlParameter("id", id)
                };
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationInstrumentRecordByIReader(iReader);
                    };
                    return null;
                }
            }

            public static void Add(InstrumentRecord entity){
                  var sql = @"insert into dbo.InstrumentRecord values (@houseid,@instrumenttype,@lastvalue,@currentvalue,@lastdate,@currentdate,@creationdate,@lastupdate,@creationuserid,@lastactionuserid) ;   select SCOPE_IDENTITY (); ";
                  var paramList = new SqlParameter[] {
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("currentdate",entity.CurrentDate),
							new SqlParameter("currentvalue",entity.CurrentValue),
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("instrumenttype",entity.InstrumentType),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastdate",entity.LastDate),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("lastvalue",entity.LastValue)
                        };
                  using ( var ireader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                            paramList)) {
                        if (ireader.Read()) {
                            entity.InstrumentRecordID = int.Parse(ireader[0].ToString());
                        }
                  }
            
            }   

            public static void Remove(InstrumentRecord entity) {
                var sql = @"delete from dbo.InstrumentRecord where InstrumentRecordID = @id";
                var paramList = new[] {
                    new SqlParameter("id", entity.InstrumentRecordID)
                };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }   

            public static IEnumerable<InstrumentRecord> LoadAll(){
                    var sql = " select * from dbo.InstrumentRecord ";
                    var list = new List<InstrumentRecord>( );
                    using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql)) {
                        while (iReader.Read( )) {
                                list.Add(CreationInstrumentRecordByIReader(iReader));
                        };
                    }  
                    return list;
            }   
        
            public static void Update(InstrumentRecord entity){
                var sql = @" update dbo.InstrumentRecord set CreationDate=@creationdate,CreationUserID=@creationuserid,CurrentDate=@currentdate,CurrentValue=@currentvalue,HouseID=@houseid,InstrumentType=@instrumenttype,LastActionUserID=@lastactionuserid,LastDate=@lastdate,LastUpdate=@lastupdate,LastValue=@lastvalue where InstrumentRecordID = @id ";
                var paramList = new SqlParameter[] {
                            new SqlParameter("id",entity.InstrumentRecordID),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("currentdate",entity.CurrentDate),
							new SqlParameter("currentvalue",entity.CurrentValue),
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("instrumenttype",entity.InstrumentType),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastdate",entity.LastDate),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("lastvalue",entity.LastValue)
                        };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }
            
            public static bool Exists(InstrumentRecord entity){
                return entity.InstrumentRecordID != 0 && GetById(entity.InstrumentRecordID)!=null;
            }


            private static InstrumentRecord CreationInstrumentRecordByIReader(IDataReader iReader){
                if(iReader!=null){
                      return new InstrumentRecord{
							CreationDate = iReader["CreationDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CreationDate"] ,
							CreationUserID = iReader["CreationUserID"] is DBNull ? default(Int32) : (Int32)iReader["CreationUserID"] ,
							CurrentDate = iReader["CurrentDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CurrentDate"] ,
							CurrentValue = iReader["CurrentValue"] is DBNull ? default(Int32) : (Int32)iReader["CurrentValue"] ,
							HouseID = iReader["HouseID"] is DBNull ? default(Int32) : (Int32)iReader["HouseID"] ,
							InstrumentRecordID = iReader["InstrumentRecordID"] is DBNull ? default(Int32) : (Int32)iReader["InstrumentRecordID"] ,
							InstrumentType = iReader["InstrumentType"] is DBNull ? default(InstrumentType) : (InstrumentType)iReader["InstrumentType"] ,
							LastActionUserID = iReader["LastActionUserID"] is DBNull ? default(Int32) : (Int32)iReader["LastActionUserID"] ,
							LastDate = iReader["LastDate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastDate"] ,
							LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"] ,
							LastValue = iReader["LastValue"] is DBNull ? default(Int32) : (Int32)iReader["LastValue"] 
                        };
                }
                return null; 
            }

            
            
            public static InstrumentRecord GetByPKs(InstrumentRecord entity){
                var sql =  @"select *  from dbo.InstrumentRecord where HouseID = @houseid and InstrumentType = @instrumenttype and CurrentDate = @currentdate ";
                var paramList = new SqlParameter[] {
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("instrumenttype",entity.InstrumentType),
							new SqlParameter("currentdate",entity.CurrentDate)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationInstrumentRecordByIReader(iReader);
                    };
                    return null;
                }
            }

            public static bool ExistsByPKs(InstrumentRecord entity){
                var sql =  @"select *  from dbo.InstrumentRecord where HouseID = @houseid and InstrumentType = @instrumenttype and CurrentDate = @currentdate ";
                var paramList = new SqlParameter[] {
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("instrumenttype",entity.InstrumentType),
							new SqlParameter("currentdate",entity.CurrentDate)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    return iReader.Read( );
                }
            }

    }
}